﻿using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using RBS.DBUtility;
using RBS.Common;
namespace RBS.Core.DAL.Hotel
{
    //菜谱表
    public partial class H_Hotel_Cookbook
    {

        public bool Exists(int Id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from H_Hotel_Cookbook");
            strSql.Append(" where ");
            strSql.Append(" Id = @Id  ");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }



        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(RBS.Core.Model.Hotel.H_Hotel_Cookbook model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into H_Hotel_Cookbook(");
            strSql.Append("Name,Price,Species,Unit,Component,DishImages,CookingWeights,Permissions,GoodCook,Special,Cuisine,Discount,EstimateClear,CookingNumber,StartIdentification,CategoryId");
            strSql.Append(") values (");
            strSql.Append("@Name,@Price,@Species,@Unit,@Component,@DishImages,@CookingWeights,@Permissions,@GoodCook,@Special,@Cuisine,@Discount,@EstimateClear,@CookingNumber,@StartIdentification,@CategoryId");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@Name", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Price", SqlDbType.Decimal,5) ,            
                        new SqlParameter("@Species", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Unit", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Component", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@DishImages", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@CookingWeights", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Permissions", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@GoodCook", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Special", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cuisine", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Discount",SqlDbType.Decimal,5) ,            
                        new SqlParameter("@EstimateClear", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CookingNumber", SqlDbType.Int,4) ,            
                        new SqlParameter("@StartIdentification", SqlDbType.Int,4) ,            
                        new SqlParameter("@CategoryId", SqlDbType.Int,4)             
              
            };

            parameters[0].Value = model.Name;
            parameters[1].Value = model.Price;
            parameters[2].Value = model.Species;
            parameters[3].Value = model.Unit;
            parameters[4].Value = model.Component;
            parameters[5].Value = model.DishImages;
            parameters[6].Value = model.CookingWeights;
            parameters[7].Value = model.Permissions;
            parameters[8].Value = model.GoodCook;
            parameters[9].Value = model.Special;
            parameters[10].Value = model.Cuisine;
            parameters[11].Value = model.Discount;
            parameters[12].Value = model.EstimateClear;
            parameters[13].Value = model.CookingNumber;
            parameters[14].Value = model.StartIdentification;
            parameters[15].Value = model.CategoryId;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {

                return Convert.ToInt32(obj);

            }

        }


        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(RBS.Core.Model.Hotel.H_Hotel_Cookbook model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update H_Hotel_Cookbook set ");

            strSql.Append(" Name = @Name , ");
            strSql.Append(" Price = @Price , ");
            strSql.Append(" Species = @Species , ");
            strSql.Append(" Unit = @Unit , ");
            strSql.Append(" Component = @Component , ");
            strSql.Append(" DishImages = @DishImages , ");
            strSql.Append(" CookingWeights = @CookingWeights , ");
            strSql.Append(" Permissions = @Permissions , ");
            strSql.Append(" GoodCook = @GoodCook , ");
            strSql.Append(" Special = @Special , ");
            strSql.Append(" Cuisine = @Cuisine , ");
            strSql.Append(" Discount = @Discount , ");
            strSql.Append(" EstimateClear = @EstimateClear , ");
            strSql.Append(" CookingNumber = @CookingNumber , ");
            strSql.Append(" StartIdentification = @StartIdentification , ");
            strSql.Append(" CategoryId = @CategoryId  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@Name", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Price", SqlDbType.Decimal,5) ,            
                        new SqlParameter("@Species", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Unit", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Component", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@DishImages", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@CookingWeights", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Permissions", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@GoodCook", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Special", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Cuisine", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Discount", SqlDbType.Decimal,5) ,            
                        new SqlParameter("@EstimateClear", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@CookingNumber", SqlDbType.Int,4) ,            
                        new SqlParameter("@StartIdentification", SqlDbType.Int,4) ,            
                        new SqlParameter("@CategoryId", SqlDbType.Int,4)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Price;
            parameters[3].Value = model.Species;
            parameters[4].Value = model.Unit;
            parameters[5].Value = model.Component;
            parameters[6].Value = model.DishImages;
            parameters[7].Value = model.CookingWeights;
            parameters[8].Value = model.Permissions;
            parameters[9].Value = model.GoodCook;
            parameters[10].Value = model.Special;
            parameters[11].Value = model.Cuisine;
            parameters[12].Value = model.Discount;
            parameters[13].Value = model.EstimateClear;
            parameters[14].Value = model.CookingNumber;
            parameters[15].Value = model.StartIdentification;
            parameters[16].Value = model.CategoryId;
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from H_Hotel_Cookbook ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;


            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from H_Hotel_Cookbook ");
            strSql.Append(" where ID in (" + Idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public RBS.Core.Model.Hotel.H_Hotel_Cookbook GetModel(int Id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select Id, Name, Price, Species, Unit, Component, DishImages, CookingWeights, Permissions, GoodCook, Special, Cuisine, Discount, EstimateClear, CookingNumber, StartIdentification, CategoryId  ");
            strSql.Append("  from H_Hotel_Cookbook ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;


            RBS.Core.Model.Hotel.H_Hotel_Cookbook model = new RBS.Core.Model.Hotel.H_Hotel_Cookbook();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);

            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["Id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["Id"].ToString());
                }
                model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                if (ds.Tables[0].Rows[0]["Price"].ToString() != "")
                {
                    model.Price = decimal.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                }
                model.Species = ds.Tables[0].Rows[0]["Species"].ToString();
                model.Unit = ds.Tables[0].Rows[0]["Unit"].ToString();
                model.Component = ds.Tables[0].Rows[0]["Component"].ToString();
                model.DishImages = ds.Tables[0].Rows[0]["DishImages"].ToString();
                model.CookingWeights = ds.Tables[0].Rows[0]["CookingWeights"].ToString();
                model.Permissions = ds.Tables[0].Rows[0]["Permissions"].ToString();
                model.GoodCook = ds.Tables[0].Rows[0]["GoodCook"].ToString();
                model.Special = ds.Tables[0].Rows[0]["Special"].ToString();
                model.Cuisine = ds.Tables[0].Rows[0]["Cuisine"].ToString();

                if (ds.Tables[0].Rows[0]["Discount"].ToString() != "")
                {
                    model.Discount = decimal.Parse(ds.Tables[0].Rows[0]["Discount"].ToString());
                }
               
                model.EstimateClear = ds.Tables[0].Rows[0]["EstimateClear"].ToString();
                if (ds.Tables[0].Rows[0]["CookingNumber"].ToString() != "")
                {
                    model.CookingNumber = int.Parse(ds.Tables[0].Rows[0]["CookingNumber"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartIdentification"].ToString() != "")
                {
                    model.StartIdentification = int.Parse(ds.Tables[0].Rows[0]["StartIdentification"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CategoryId"].ToString() != "")
                {
                    model.CategoryId = int.Parse(ds.Tables[0].Rows[0]["CategoryId"].ToString());
                }

                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM H_Hotel_Cookbook ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM H_Hotel_Cookbook ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }


        #region  ExtensionMethod
        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM H_Hotel_Cookbook ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where 1=1 " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion  ExtensionMethod


        /// <summary>
        /// 获取菜谱上一条和下一条数据
        /// </summary>
        /// <param name="hid"></param>
        /// <param name="cid"></param>
        /// <returns></returns>
        public DataSet GetBeforeAndAfter(int hid, int cid)
        {
            const string strsql = @"WITH
                            PREVC as(
                            select top 1 @hid as hid,c.id as PrevId from H_Hotel_Cookbook c,H_Hotel_CookbookCategory cc where c.categoryid=cc.id and c.id <@cid and cc.hotelid=@hid  and isdisplay=0 order by c.id desc
                            ),
                            nextc
                            as
                            (
                             select top 1 @hid as hid,c.id as NextId from H_Hotel_Cookbook c,H_Hotel_CookbookCategory cc where c.categoryid=cc.id and c.id >@cid and cc.hotelid=@hid  and isdisplay=0 order by c.id asc
                            )
                            select * from PREVC FULL JOIN nextc on PREVC.hid=nextc.hid";

            SqlParameter[] parameters = {
					new SqlParameter("@cid", SqlDbType.Int,4),
                    new SqlParameter("@hid", SqlDbType.Int,4)
			};
            parameters[0].Value = cid;
            parameters[1].Value = hid;  
            DataSet ds = DbHelperSQL.Query(strsql, parameters); 
            return ds;
        }
    }
}

